package com.pig4cloud.pig.module.service.property.impl;

import cn.hutool.core.lang.Validator;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.pig4cloud.pig.common.core.exception.CustomException;
import com.pig4cloud.pig.common.core.exception.TransactionalException;
import com.pig4cloud.pig.module.api.entity.estate.Estate;
import com.pig4cloud.pig.module.api.entity.estate.EstateRoom;
import com.pig4cloud.pig.module.api.entity.excel.PropertySdJyTemplateExcel;
import com.pig4cloud.pig.module.api.entity.excel.PropertyWyfTemplateExcel;
import com.pig4cloud.pig.module.api.entity.excel.PropertyWyfYearTemplateExcel;
import com.pig4cloud.pig.module.api.entity.property.PropertyFeeOrderTemplate;
import com.pig4cloud.pig.module.mapper.estate.EstateMapper;
import com.pig4cloud.pig.module.mapper.estate.EstateRoomMapper;
import com.pig4cloud.pig.module.mapper.property.PropertyFeeOrderTemplateMapper;
import com.pig4cloud.pig.module.service.property.IPropertyFeeOrderTemplateService;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

/**
 * 物业费相关费用的模板Service业务层处理
 *
 * @author ruoyi
 * @date 2022-06-27
 */
@Service
public class PropertyFeeOrderTemplateServiceImpl extends ServiceImpl<PropertyFeeOrderTemplateMapper, PropertyFeeOrderTemplate> implements IPropertyFeeOrderTemplateService {

	@Resource
	private PropertyFeeOrderTemplateMapper orderTemplateMapper;
	@Resource
	private EstateMapper estateMapper;
	@Resource
	private EstateRoomMapper estateRoomMapper;

	/**
	 * 导入模板数据
	 *
	 * @param templateList 用户数据列表
	 * @param isUpdateSupport 是否更新支持，如果已存在，则进行更新数据
	 * @param operName 操作用户
	 * @return 结果
	 */
	public String importWyfData(List<PropertyWyfTemplateExcel> templateList,Integer estateId, Boolean isUpdateSupport, String operName) {
		if (Validator.isNull(templateList) || templateList.size() == 0) {
			throw new CustomException("导入模板数据不能为空！");
		}
		Estate estate = estateMapper.selectById(estateId);
		if(estate == null){
			throw new CustomException("所属小区不存在！");
		}

		int ret = 0;
		int successNum = 0;
		int failureNum = 0;
		StringBuilder successMsg = new StringBuilder();
		StringBuilder failureMsg = new StringBuilder();
		for (PropertyWyfTemplateExcel template : templateList) {
			try {
				String roomNo = template.getRoomNo().replace(" ","");
				if(StrUtil.isEmpty(roomNo)){
					continue;
				}
				boolean blRoomExits = true;
				QueryWrapper<EstateRoom> qwRoom = new QueryWrapper<>();
				qwRoom.eq("estate_id",estateId);
				qwRoom.eq("room_no", roomNo);
				qwRoom.last("limit 1");
				EstateRoom room = estateRoomMapper.selectOne(qwRoom);
				if(room == null){
					blRoomExits = false;
					room = new EstateRoom();
					room.setEstateId(estateId);
					room.setEstateName(estate.getEstateName());
					room.setPhoneNum(template.getPhoneNum());
					room.setRealName(template.getRealName());
					room.setRoomNo(roomNo);
					room.setState(1);
					room.setBangWxUserId(0);
				}
				if(StrUtil.isNotEmpty(template.getRoomArea())){
					room.setRoomArea(template.getRoomArea());
				}
				if(StrUtil.isNotEmpty(roomNo)){
					String[] roomNums = roomNo.split("-");
					if(roomNums.length >= 3){
						room.setBuildNo(roomNums[0]);
						room.setUnitNo(roomNums[1]);
						room.setRoomNum(roomNums[2]);
					}
				}

				if(StrUtil.isNotEmpty(template.getRoomType())){
					//房屋类型  0 - 高层 1 - 多层 2- 商业 3 - 别墅 4 - 其他
					switch (template.getRoomType()){
						case "高层": room.setRoomType(0);break;
						case "多层": room.setRoomType(1);break;
						case "商业": room.setRoomType(2);break;
						case "别墅": room.setRoomType(3);break;
						case "其他": room.setRoomType(4);break;
					}
				}

				room.setPhoneNum(template.getPhoneNum());
				room.setRealName(template.getRealName());
				room.setRoomNo(roomNo);
				if(blRoomExits){
					ret = estateRoomMapper.updateById(room);
				}else{
					if(StrUtil.isNotEmpty(roomNo)){
						ret = estateRoomMapper.insert(room);
					}
				}
				if(ret <= 0){
					throw new TransactionalException("orderTemplateMapper insert error");
				}

				//费用类型 wyf - 物业费 rubbish - 垃圾处理费 car - 车位费
				//物业费
				if(StrUtil.isNotEmpty(template.getWyfPrice())) {
					double totalPrice = Double.parseDouble(template.getWyfPrice());
					if(totalPrice >= 0){
						boolean blWyfExits = true;
						QueryWrapper<PropertyFeeOrderTemplate> qw = new QueryWrapper<>();
//						qw.eq("real_name", template.getRealName());
						qw.eq("room_no", roomNo);
						qw.eq("fee_type", "wyf");
						qw.eq("year_num", template.getWyfYearNum());
						qw.eq("month_num", template.getWyfMonthNum());
						qw.eq("estate_id", estate.getId());
						qw.eq("is_garage", 0);
						if(StrUtil.isNotEmpty(template.getIsGarage())){
							if(template.getIsGarage().equals("是")){
								qw.eq("is_garage", 1);
							}
						}
						qw.last("limit 1");
						PropertyFeeOrderTemplate wyf = orderTemplateMapper.selectOne(qw);
						if (wyf == null) {
							blWyfExits = false;
							wyf = new PropertyFeeOrderTemplate();
							wyf.setYearNum(template.getWyfYearNum());
							wyf.setMonthNum(template.getWyfMonthNum());
							wyf.setRoomType(room.getRoomType());
							wyf.setEstateId(estateId);
							wyf.setEstateName(estate.getEstateName());
							wyf.setFeeType("wyf");
							wyf.setRoomId(room.getId());
							wyf.setRoomNo(roomNo);
							wyf.setPhoneNum(template.getPhoneNum());
							wyf.setRealName(template.getRealName());

							wyf.setBangWxUserId(0);
							wyf.setRemark(template.getRemark());
							wyf.setIsBand(0);
							wyf.setState(0);
							wyf.setCreateTime(new Date());
							wyf.setPaidPrice(new BigDecimal(0));

							wyf.setIsManualData(0);//批量导入
						}
						wyf.setIsArrears(getIsArrears(template.getWyfYearNum(),template.getWyfMonthNum()));


						wyf.setIsGarage(0);
						if(StrUtil.isNotEmpty(template.getIsGarage())){
							if(template.getIsGarage().equals("是")){
								wyf.setIsGarage(1);
							}
						}


						wyf.setRoomId(room.getId());
						if (StrUtil.isNotEmpty(template.getRoomArea())) {
							wyf.setRoomArea(Double.parseDouble(template.getRoomArea()));
						}
						if (StrUtil.isNotEmpty(template.getWyfSinglePrice())) {
							wyf.setSinglePrice(new BigDecimal(template.getWyfSinglePrice()));
						}
						if (StrUtil.isNotEmpty(template.getWyfPrice())) {
							wyf.setTotalPrice(new BigDecimal(template.getWyfPrice()));
						}
						if (StrUtil.isNotEmpty(template.getWyfYouHuiPrice())) {
							wyf.setYouhuiPrice(new BigDecimal(template.getWyfYouHuiPrice()));
						}
						if (blWyfExits) {//存在更新
							ret = orderTemplateMapper.updateById(wyf);
						} else {
							ret = orderTemplateMapper.insert(wyf);
						}
						if (ret <= 0) {
							throw new TransactionalException("orderTemplateMapper wyf insert error");
						}
					}
				}

				//垃圾处理费
				if(StrUtil.isNotEmpty(template.getRubbishPrice())) {
					double totalPrice = Double.parseDouble(template.getRubbishPrice());
					if(totalPrice >= 0){
						boolean blRubbishExits = true;
						QueryWrapper<PropertyFeeOrderTemplate> qwRubbish = new QueryWrapper<>();
//						qwRubbish.eq("real_name", template.getRealName());
						qwRubbish.eq("room_no", roomNo);
						qwRubbish.eq("fee_type", "rubbish");
						qwRubbish.eq("year_num", template.getRubbishYearNum());
						qwRubbish.eq("month_num", template.getRubbishMonthNum());
						qwRubbish.eq("estate_id", estate.getId());
						qwRubbish.eq("is_garage", 0);
						if(StrUtil.isNotEmpty(template.getIsGarage())){
							if(template.getIsGarage().equals("是")){
								qwRubbish.eq("is_garage", 1);
							}
						}
						qwRubbish.last("limit 1");
						PropertyFeeOrderTemplate rubbish = orderTemplateMapper.selectOne(qwRubbish);
						if (rubbish == null) {
							blRubbishExits = false;
							rubbish = new PropertyFeeOrderTemplate();
							rubbish.setEstateId(estateId);
							rubbish.setEstateName(estate.getEstateName());

							rubbish.setYearNum(template.getRubbishYearNum());
							rubbish.setMonthNum(template.getRubbishMonthNum());
							rubbish.setFeeType("rubbish");

							rubbish.setPhoneNum(template.getPhoneNum());
							rubbish.setRealName(template.getRealName());
							rubbish.setRoomNo(roomNo);
							rubbish.setRoomType(room.getRoomType());
							rubbish.setBangWxUserId(0);
							rubbish.setRemark(template.getRemark());
							rubbish.setIsBand(0);
							rubbish.setState(0);
							rubbish.setCreateTime(new Date());
							rubbish.setPaidPrice(new BigDecimal(0));
							rubbish.setRemark(template.getRemark());
							rubbish.setIsManualData(0);//批量导入
						}
						rubbish.setIsArrears(getIsArrears(template.getRubbishYearNum(),template.getRubbishMonthNum()));
						rubbish.setRoomId(room.getId());


						rubbish.setIsGarage(0);
						if(StrUtil.isNotEmpty(template.getIsGarage())){
							if(template.getIsGarage().equals("是")){
								rubbish.setIsGarage(1);
							}
						}

						if (StrUtil.isNotEmpty(template.getRoomArea())) {
							rubbish.setRoomArea(Double.parseDouble(template.getRoomArea()));
						}
						if (StrUtil.isNotEmpty(template.getRubbishSinglePrice())) {
							rubbish.setSinglePrice(new BigDecimal(template.getRubbishSinglePrice()));
						}
						if (StrUtil.isNotEmpty(template.getRubbishPrice())) {
							rubbish.setTotalPrice(new BigDecimal(template.getRubbishPrice()));
						}
						if (StrUtil.isNotEmpty(template.getRubbishYouHuiPrice())) {
							rubbish.setYouhuiPrice(new BigDecimal(template.getRubbishYouHuiPrice()));
						}
						if (blRubbishExits) {//存在更新
							ret = orderTemplateMapper.updateById(rubbish);
						} else {
							ret = orderTemplateMapper.insert(rubbish);
						}
						if (ret <= 0) {
							throw new TransactionalException("orderTemplateMapper insert error");
						}
					}
				}

				//车位费
				if(StrUtil.isNotEmpty(template.getCarPrice())) {
					double totalPrice = Double.parseDouble(template.getCarPrice());
					if(totalPrice >= 0){
						boolean blCarExits = true;
						QueryWrapper<PropertyFeeOrderTemplate> qwCar = new QueryWrapper<>();
//						qwCar.eq("real_name", template.getRealName());
						qwCar.eq("room_no", roomNo);
						qwCar.eq("fee_type", "car");
						qwCar.eq("year_num", template.getCarYearNum());
						qwCar.eq("month_num", template.getCarMonthNum());
						qwCar.eq("estate_id", estate.getId());
						qwCar.eq("is_garage", 0);
						if(StrUtil.isNotEmpty(template.getIsGarage())){
							if(template.getIsGarage().equals("是")){
								qwCar.eq("is_garage", 1);
							}
						}
						qwCar.last("limit 1");
						PropertyFeeOrderTemplate car = orderTemplateMapper.selectOne(qwCar);
						if (car == null) {
							blCarExits = false;
							car = new PropertyFeeOrderTemplate();
							car.setEstateId(estateId);
							car.setEstateName(estate.getEstateName());

							car.setFeeType("car");
							car.setPhoneNum(template.getPhoneNum());
							car.setRealName(template.getRealName());
							car.setRoomNo(roomNo);
							car.setRoomType(room.getRoomType());

							car.setYearNum(template.getCarYearNum());
							car.setMonthNum(template.getCarMonthNum());

							car.setBangWxUserId(0);
							car.setRemark(template.getRemark());
							car.setIsBand(0);
							car.setState(0);
							car.setCreateTime(new Date());
							car.setPaidPrice(new BigDecimal(0));
							car.setIsManualData(0);//批量导入
						}

						car.setIsArrears(getIsArrears(template.getCarYearNum(),template.getCarMonthNum()));
						car.setCarNum(template.getCarNum());
						car.setRoomId(room.getId());

						car.setIsGarage(0);
						if(StrUtil.isNotEmpty(template.getIsGarage())){
							if(template.getIsGarage().equals("是")){
								car.setIsGarage(1);
							}
						}

						if (StrUtil.isNotEmpty(template.getRoomArea())) {
							car.setRoomArea(Double.parseDouble(template.getRoomArea()));
						}
						if (StrUtil.isNotEmpty(template.getCarSinglePrice())) {
							car.setSinglePrice(new BigDecimal(template.getCarSinglePrice()));
						}
						if (StrUtil.isNotEmpty(template.getCarPrice())) {
							car.setTotalPrice(new BigDecimal(template.getCarPrice()));
						}
						if (StrUtil.isNotEmpty(template.getCarYouHuiPrice())) {
							car.setYouhuiPrice(new BigDecimal(template.getCarYouHuiPrice()));
						}
						if (StrUtil.isNotEmpty(template.getCarCount())) {
							car.setPaidCount(new BigDecimal(template.getCarCount()));
						}
						if (blCarExits) {
							ret = orderTemplateMapper.updateById(car);
						} else {
							ret = orderTemplateMapper.insert(car);
						}
					}
				}
				if(ret <= 0){
					throw new TransactionalException("orderTemplateMapper insert error");
				}
				successNum++;
			} catch (Exception e) {
				failureNum++;
				String msg = "<br/>" + failureNum + "、账号 " + template.getRoomNo() + "-" + template.getRealName() + " 导入失败：";
				failureMsg.append(msg + e.getMessage());
				log.error(msg, e);
			}
		}
		if (failureNum > 0) {
			failureMsg.insert(0, "很抱歉，导入失败！共 " + failureNum + " 条数据格式不正确，错误如下：");
			throw new CustomException(failureMsg.toString());
		} else {
			successMsg.insert(0, "恭喜您，数据已全部导入成功！共 " + successNum + " 条，数据如下：");
		}
		return successMsg.toString();
	}

	@Override
	@Transactional
	public String importWyfYearData(List<PropertyWyfYearTemplateExcel> templateList, Integer estateId, Boolean isUpdateSupport, String operName) {
		if (Validator.isNull(templateList) || templateList.size() == 0) {
			throw new CustomException("导入模板数据不能为空！");
		}
		Estate estate = estateMapper.selectById(estateId);
		if(estate == null){
			throw new CustomException("所属小区不存在！");
		}

		int ret = 0;
		int successNum = 0;
		int failureNum = 0;
		StringBuilder successMsg = new StringBuilder();
		StringBuilder failureMsg = new StringBuilder();
		for (PropertyWyfYearTemplateExcel template : templateList) {
			try {
				String roomNo = template.getRoomNo().replace(" ","");
				if(StrUtil.isEmpty(roomNo)){
					continue;
				}
				boolean blRoomExits = true;
				QueryWrapper<EstateRoom> qwRoom = new QueryWrapper<>();
				qwRoom.eq("estate_id",estateId);
				qwRoom.eq("room_no", roomNo);
				qwRoom.last("limit 1");
				EstateRoom room = estateRoomMapper.selectOne(qwRoom);
				if(room == null){
					blRoomExits = false;
					room = new EstateRoom();
					room.setEstateId(estateId);
					room.setEstateName(estate.getEstateName());
					room.setPhoneNum(template.getPhoneNum());
					room.setRealName(template.getRealName());
					room.setRoomNo(roomNo);
					room.setState(1);
					room.setBangWxUserId(0);
				}

				if(StrUtil.isNotEmpty(template.getRoomArea())){
					room.setRoomArea(template.getRoomArea());
				}
				if(StrUtil.isNotEmpty(roomNo)){
					String[] roomNums = roomNo.split("-");
					if(roomNums.length >= 3){
						room.setBuildNo(roomNums[0]);
						room.setUnitNo(roomNums[1]);
						room.setRoomNum(roomNums[2]);
					}
				}

				if(StrUtil.isNotEmpty(template.getRoomType())){
					//房屋类型  0 - 高层 1 - 多层 2- 商业 3 - 别墅 4 - 其他
					switch (template.getRoomType()){
						case "高层": room.setRoomType(0);break;
						case "多层": room.setRoomType(1);break;
						case "商业": room.setRoomType(2);break;
						case "别墅": room.setRoomType(3);break;
						case "其他": room.setRoomType(4);break;
					}
				}

				room.setPhoneNum(template.getPhoneNum());
				room.setRealName(template.getRealName());
				room.setRoomNo(roomNo);
				if(blRoomExits){
					ret = estateRoomMapper.updateById(room);
				}else{
					if(StrUtil.isNotEmpty(roomNo)) {
						ret = estateRoomMapper.insert(room);
					}
				}
				if(ret <= 0){
					throw new TransactionalException("orderTemplateMapper insert error");
				}
				if(template.getMonth() == null){
					failureNum++;
					continue;
				}

				int beginMonth = Integer.parseInt(template.getMonth());

				for (int i = beginMonth - 1; i < 12; i++){

					//费用类型 wyf - 物业费 rubbish - 垃圾处理费 car - 车位费
					//物业费
					if(StrUtil.isNotEmpty(template.getWyfPrice())) {
						double totalPrice = Double.parseDouble(template.getWyfPrice());
						if(totalPrice >= 0){
							boolean blWyfExits = true;
							QueryWrapper<PropertyFeeOrderTemplate> qw = new QueryWrapper<>();
//							qw.eq("real_name", template.getRealName());
							qw.eq("room_no", roomNo);
							qw.eq("fee_type", "wyf");
							qw.eq("year_num", template.getYear());
							qw.eq("month_num", String.valueOf((i+ 1 )));
							qw.eq("estate_id", estate.getId());
							qw.eq("is_garage", 0);
							if(StrUtil.isNotEmpty(template.getIsGarage())){
								if(template.getIsGarage().equals("是")){
									qw.eq("is_garage", 1);
								}
							}
							qw.last("limit 1");
							PropertyFeeOrderTemplate wyf = orderTemplateMapper.selectOne(qw);
							if (wyf == null) {
								blWyfExits = false;
								wyf = new PropertyFeeOrderTemplate();
								wyf.setYearNum(template.getYear());
								wyf.setMonthNum( String.valueOf((i+1)));
								wyf.setRoomType(room.getRoomType());
								wyf.setEstateId(estateId);
								wyf.setEstateName(estate.getEstateName());
								wyf.setFeeType("wyf");
								wyf.setRoomId(room.getId());
								wyf.setRoomNo(roomNo);
								wyf.setPhoneNum(template.getPhoneNum());
								wyf.setRealName(template.getRealName());

								wyf.setBangWxUserId(0);
								wyf.setRemark(template.getRemark());
								wyf.setIsBand(0);
								wyf.setState(0);
								wyf.setCreateTime(new Date());
								wyf.setPaidPrice(new BigDecimal(0));
								wyf.setIsManualData(0);//批量导入
							}
							wyf.setIsArrears(getIsArrears(template.getYear(),template.getMonth()));

							wyf.setRoomId(room.getId());

							wyf.setIsGarage(0);
							if(StrUtil.isNotEmpty(template.getIsGarage())){
								if(template.getIsGarage().equals("是")){
									wyf.setIsGarage(1);
								}
							}

							if (StrUtil.isNotEmpty(template.getRoomArea())) {
								wyf.setRoomArea(Double.parseDouble(template.getRoomArea()));
							}
							if (StrUtil.isNotEmpty(template.getWyfSinglePrice())) {
								wyf.setSinglePrice(new BigDecimal(template.getWyfSinglePrice()));
							}
							if (StrUtil.isNotEmpty(template.getWyfPrice())) {
								wyf.setTotalPrice(new BigDecimal(template.getWyfPrice()));
							}
							if (StrUtil.isNotEmpty(template.getWyfYouHuiPrice())) {
								wyf.setYouhuiPrice(new BigDecimal(template.getWyfYouHuiPrice()));
							}
							if (blWyfExits) {//存在更新
								ret = orderTemplateMapper.updateById(wyf);
							} else {
								ret = orderTemplateMapper.insert(wyf);
							}
							if (ret <= 0) {
								throw new TransactionalException("orderTemplateMapper wyf insert error");
							}
						}
					}

					//垃圾处理费
					if(StrUtil.isNotEmpty(template.getRubbishPrice())) {

						double totalPrice = Double.parseDouble(template.getRubbishPrice());
						if(totalPrice >= 0){
							boolean blRubbishExits = true;
							QueryWrapper<PropertyFeeOrderTemplate> qwRubbish = new QueryWrapper<>();
//							qwRubbish.eq("real_name", template.getRealName());
							qwRubbish.eq("room_no", roomNo);
							qwRubbish.eq("fee_type", "rubbish");
							qwRubbish.eq("year_num", template.getYear());
							qwRubbish.eq("month_num", String.valueOf((i+1)));
							qwRubbish.eq("estate_id", estate.getId());
							qwRubbish.eq("is_garage", 0);
							if(StrUtil.isNotEmpty(template.getIsGarage())){
								if(template.getIsGarage().equals("是")){
									qwRubbish.eq("is_garage", 1);
								}
							}
							qwRubbish.last("limit 1");
							PropertyFeeOrderTemplate rubbish = orderTemplateMapper.selectOne(qwRubbish);
							if (rubbish == null) {
								blRubbishExits = false;
								rubbish = new PropertyFeeOrderTemplate();
								rubbish.setEstateId(estateId);
								rubbish.setEstateName(estate.getEstateName());

								rubbish.setYearNum(template.getYear());
								rubbish.setMonthNum(String.valueOf((i+1)));
								rubbish.setFeeType("rubbish");

								rubbish.setPhoneNum(template.getPhoneNum());
								rubbish.setRealName(template.getRealName());
								rubbish.setRoomNo(roomNo);
								rubbish.setRoomType(room.getRoomType());
								rubbish.setBangWxUserId(0);
								rubbish.setRemark(template.getRemark());
								rubbish.setIsBand(0);
								rubbish.setState(0);
								rubbish.setCreateTime(new Date());
								rubbish.setPaidPrice(new BigDecimal(0));
								rubbish.setRemark(template.getRemark());
								rubbish.setIsManualData(0);//批量导入
							}
							rubbish.setIsArrears(getIsArrears(template.getYear(),template.getMonth()));
							rubbish.setRoomId(room.getId());

							rubbish.setIsGarage(0);
							if(StrUtil.isNotEmpty(template.getIsGarage())){
								if(template.getIsGarage().equals("是")){
									rubbish.setIsGarage(1);
								}
							}

							if (StrUtil.isNotEmpty(template.getRoomArea())) {
								rubbish.setRoomArea(Double.parseDouble(template.getRoomArea()));
							}
							if (StrUtil.isNotEmpty(template.getRubbishSinglePrice())) {
								rubbish.setSinglePrice(new BigDecimal(template.getRubbishSinglePrice()));
							}
							if (StrUtil.isNotEmpty(template.getRubbishPrice())) {
								rubbish.setTotalPrice(new BigDecimal(template.getRubbishPrice()));
							}
							if (StrUtil.isNotEmpty(template.getRubbishYouHuiPrice())) {
								rubbish.setYouhuiPrice(new BigDecimal(template.getRubbishYouHuiPrice()));
							}
							if (blRubbishExits) {//存在更新
								ret = orderTemplateMapper.updateById(rubbish);
							} else {
								ret = orderTemplateMapper.insert(rubbish);
							}
							if (ret <= 0) {
								throw new TransactionalException("orderTemplateMapper insert error");
							}
						}
					}

					//车位费
					if(StrUtil.isNotEmpty(template.getCarPrice())) {
						double totalPrice = Double.parseDouble(template.getCarPrice());
						if(totalPrice >= 0){
							boolean blCarExits = true;
							QueryWrapper<PropertyFeeOrderTemplate> qwCar = new QueryWrapper<>();
//							qwCar.eq("real_name", template.getRealName());
							qwCar.eq("room_no", roomNo);
							qwCar.eq("fee_type", "car");
							qwCar.eq("year_num", template.getYear());
							qwCar.eq("month_num", String.valueOf((i+1)));
							qwCar.eq("estate_id", estate.getId());
							qwCar.eq("is_garage", 0);
							if(StrUtil.isNotEmpty(template.getIsGarage())){
								if(template.getIsGarage().equals("是")){
									qwCar.eq("is_garage", 1);
								}
							}
							qwCar.last("limit 1");
							PropertyFeeOrderTemplate car = orderTemplateMapper.selectOne(qwCar);
							if (car == null) {
								blCarExits = false;
								car = new PropertyFeeOrderTemplate();
								car.setEstateId(estateId);
								car.setEstateName(estate.getEstateName());

								car.setFeeType("car");
								car.setPhoneNum(template.getPhoneNum());
								car.setRealName(template.getRealName());
								car.setRoomNo(roomNo);
								car.setRoomType(room.getRoomType());

								car.setYearNum(template.getYear());
								car.setMonthNum(String.valueOf((i+1)));

								car.setBangWxUserId(0);
								car.setRemark(template.getRemark());
								car.setIsBand(0);
								car.setState(0);
								car.setCreateTime(new Date());
								car.setPaidPrice(new BigDecimal(0));
								car.setIsManualData(0);//批量导入
							}
							car.setIsArrears(getIsArrears(template.getYear(),template.getMonth()));
							car.setCarNum(template.getCarNum());
							car.setRoomId(room.getId());

							car.setIsGarage(0);
							if(StrUtil.isNotEmpty(template.getIsGarage())){
								if(template.getIsGarage().equals("是")){
									car.setIsGarage(1);
								}
							}


							if (StrUtil.isNotEmpty(template.getRoomArea())) {
								car.setRoomArea(Double.parseDouble(template.getRoomArea()));
							}
							if (StrUtil.isNotEmpty(template.getCarSinglePrice())) {
								car.setSinglePrice(new BigDecimal(template.getCarSinglePrice()));
							}
							if (StrUtil.isNotEmpty(template.getCarPrice())) {
								car.setTotalPrice(new BigDecimal(template.getCarPrice()));
							}
							if (StrUtil.isNotEmpty(template.getCarYouHuiPrice())) {
								car.setYouhuiPrice(new BigDecimal(template.getCarYouHuiPrice()));
							}
							if (StrUtil.isNotEmpty(template.getCarCount())) {
								car.setPaidCount(new BigDecimal(template.getCarCount()));
							}
							if (blCarExits) {
								ret = orderTemplateMapper.updateById(car);
							} else {
								ret = orderTemplateMapper.insert(car);
							}
						}
					}

				}

				if(ret <= 0){
					throw new TransactionalException("orderTemplateMapper insert error");
				}
				successNum++;

			} catch (Exception e) {
				failureNum++;
				String msg = "<br/>" + failureNum + "、账号 " + template.getRoomNo() + "-" + template.getRealName() + " 导入失败：";
				failureMsg.append(msg + e.getMessage());
				log.error(msg, e);
			}
		}
		if (failureNum > 0) {
			failureMsg.insert(0, "很抱歉，导入失败！共 " + failureNum + " 条数据格式不正确，错误如下：");
			throw new CustomException(failureMsg.toString());
		} else {
			successMsg.insert(0, "恭喜您，数据已全部导入成功！共 " + successNum + " 条，数据如下：");
		}
		return successMsg.toString();
	}

	@Override
	@Transactional
	public String importSdJyData(List<PropertySdJyTemplateExcel> templateList,Integer estateId, Boolean isUpdateSupport, String operName) {
		if (Validator.isNull(templateList) || templateList.size() == 0) {
			throw new CustomException("导入模板数据不能为空！");
		}
		int ret = 0;

		Estate estate = estateMapper.selectById(estateId);
		if(estate == null){
			throw new CustomException("所属小区不存在！");
		}

		int successNum = 0;
		int failureNum = 0;
		StringBuilder successMsg = new StringBuilder();
		StringBuilder failureMsg = new StringBuilder();
		for (PropertySdJyTemplateExcel template : templateList) {
			try {
				String roomNo = template.getRoomNo().replace(" ","");
				if(StrUtil.isEmpty(roomNo)){
					continue;
				}
				boolean blRoomExits = true;
				QueryWrapper<EstateRoom> qwRoom = new QueryWrapper<>();
				qwRoom.eq("estate_id",estateId);
				qwRoom.eq("room_no", roomNo);
				qwRoom.last("limit 1");
				EstateRoom room = estateRoomMapper.selectOne(qwRoom);
				if(room == null){
					blRoomExits = false;
					room = new EstateRoom();
					room.setEstateId(estateId);
					room.setEstateName(estate.getEstateName());
					room.setPhoneNum(template.getPhoneNum());
					room.setRealName(template.getRealName());
					room.setRoomNo(roomNo);
					room.setState(1);
					room.setBangWxUserId(0);
				}

				if(StrUtil.isNotEmpty(roomNo)){
					String[] roomNums = roomNo.split("-");
					if(roomNums.length >= 3){
						room.setBuildNo(roomNums[0]);
						room.setUnitNo(roomNums[1]);
						room.setRoomNum(roomNums[2]);
					}
				}


				if(StrUtil.isNotEmpty(template.getRoomType())){
					//房屋类型  0 - 高层 1 - 多层 2- 商业 3 - 别墅 4 - 其他
					switch (template.getRoomType()){
						case "高层": room.setRoomType(0);break;
						case "多层": room.setRoomType(1);break;
						case "商业": room.setRoomType(2);break;
						case "别墅": room.setRoomType(3);break;
						case "其他": room.setRoomType(4);break;
					}
				}


				room.setPhoneNum(template.getPhoneNum());
				room.setRealName(template.getRealName());
				room.setRoomNo(roomNo);
				if(blRoomExits){
					ret = estateRoomMapper.updateById(room);
				}else{
					if(StrUtil.isNotEmpty(roomNo)) {
						ret = estateRoomMapper.insert(room);
					}
				}
				if(ret <= 0){
					throw new TransactionalException("orderTemplateMapper insert error");
				}


				//water - 水费 ，eletrict - 电费 ，jiaya - 加压费
				//电费
				if(StrUtil.isNotEmpty(template.getElectricPrice())) {
					double totalPrice = Double.parseDouble(template.getElectricPrice());
					if(totalPrice >= 0){
						boolean blEletrictExits = true;
						QueryWrapper<PropertyFeeOrderTemplate> qwEletrict = new QueryWrapper<>();
//						qwEletrict.eq("real_name", template.getRealName());
						qwEletrict.eq("room_no", roomNo);
						qwEletrict.eq("fee_type", "eletrict");
						qwEletrict.eq("year_num", template.getElectricYearNum());
						qwEletrict.eq("month_num", template.getElectricMonthNum());
						qwEletrict.eq("estate_id",estate.getId());
						qwEletrict.eq("is_garage", 0);
						if(StrUtil.isNotEmpty(template.getIsGarage())){
							if(template.getIsGarage().equals("是")){
								qwEletrict.eq("is_garage", 1);
							}
						}
						qwEletrict.last("limit 1");
						PropertyFeeOrderTemplate eletrict = orderTemplateMapper.selectOne(qwEletrict);
						if(eletrict == null){
							blEletrictExits = false;
							eletrict = new PropertyFeeOrderTemplate();
							eletrict.setEstateId(estateId);
							eletrict.setEstateName(estate.getEstateName());
							eletrict.setFeeType("eletrict");

							eletrict.setRoomType(room.getRoomType());
							eletrict.setRoomNo(roomNo);
							eletrict.setRealName(template.getRealName());
							eletrict.setRoomType(room.getRoomType());
							eletrict.setPhoneNum(template.getPhoneNum());

							eletrict.setYearNum(template.getElectricYearNum());
							eletrict.setMonthNum(template.getElectricMonthNum());

							eletrict.setBangWxUserId(0);
							eletrict.setRemark(template.getRemark());
							eletrict.setIsBand(0);
							eletrict.setState(0);
							eletrict.setCreateTime(new Date());

							eletrict.setPaidPrice(new BigDecimal(0));
							eletrict.setYouhuiPrice(new BigDecimal(0));
							eletrict.setUnPayPrice(new BigDecimal(0));
							eletrict.setIsManualData(0);//批量导入
						}
						eletrict.setIsArrears(getIsArrears(template.getElectricYearNum(),template.getElectricMonthNum()));

						eletrict.setRoomId(room.getId());

						eletrict.setIsGarage(0);
						if(StrUtil.isNotEmpty(template.getIsGarage())){
							if(template.getIsGarage().equals("是")){
								eletrict.setIsGarage(1);
							}
						}

						if(StrUtil.isNotEmpty(template.getElectricBeginCount())) {
							eletrict.setBeginCount(new BigDecimal(template.getElectricBeginCount()));
						}

						if(StrUtil.isNotEmpty(template.getElectricEndCount())) {
							eletrict.setEndCount(new BigDecimal(template.getElectricEndCount()));
						}

						if(StrUtil.isNotEmpty(template.getElectricNum())) {
							eletrict.setPaidCount(new BigDecimal(template.getElectricNum()));
						}
						if(StrUtil.isNotEmpty(template.getElectricSinglePrice())){
							eletrict.setSinglePrice(new BigDecimal(template.getElectricSinglePrice()));
						}
						if(StrUtil.isNotEmpty(template.getElectricPrice())) {
							eletrict.setTotalPrice(new BigDecimal(template.getElectricPrice()));
						}
						if(blEletrictExits){
							ret = orderTemplateMapper.updateById(eletrict);
						}else{
							ret = orderTemplateMapper.insert(eletrict);
						}
						if(ret <= 0){
							throw new TransactionalException("orderTemplateMapper insert error");
						}
					}
				}

				// 水费
				if(StrUtil.isNotEmpty(template.getWaterPrice())) {
					double totalPrice = Double.parseDouble(template.getWaterPrice());
					if(totalPrice >= 0){
						boolean blWaterExits = true;
						QueryWrapper<PropertyFeeOrderTemplate> qwWater = new QueryWrapper<>();
//						qwWater.eq("real_name", template.getRealName());
						qwWater.eq("room_no", roomNo);
						qwWater.eq("fee_type", "water");
						qwWater.eq("year_num", template.getWaterYearNum());
						qwWater.eq("month_num", template.getWaterMonthNum());
						qwWater.eq("estate_id", estate.getId());
						qwWater.eq("is_garage", 0);
						if(StrUtil.isNotEmpty(template.getIsGarage())){
							if(template.getIsGarage().equals("是")){
								qwWater.eq("is_garage", 1);
							}
						}
						qwWater.last("limit 1");
						PropertyFeeOrderTemplate water = orderTemplateMapper.selectOne(qwWater);
						if (water == null) {
							blWaterExits = false;
							water = new PropertyFeeOrderTemplate();
							water.setEstateId(estateId);
							water.setEstateName(estate.getEstateName());
							water.setFeeType("water");

							water.setRoomType(room.getRoomType());
							water.setPhoneNum(template.getPhoneNum());
							water.setRealName(template.getRealName());
							water.setRoomNo(roomNo);

							water.setYearNum(template.getWaterYearNum());
							water.setMonthNum(template.getWaterMonthNum());

							water.setBangWxUserId(0);
							water.setRemark(template.getRemark());
							water.setIsBand(0);
							water.setState(0);
							water.setCreateTime(new Date());

							water.setPaidPrice(new BigDecimal(0));

							water.setYouhuiPrice(new BigDecimal(0));
							water.setUnPayPrice(new BigDecimal(0));
							water.setIsManualData(0);//批量导入

						}
						water.setIsArrears(getIsArrears(template.getWaterYearNum(),template.getWaterMonthNum()));
						water.setRoomId(room.getId());

						water.setIsGarage(0);
						if(StrUtil.isNotEmpty(template.getIsGarage())){
							if(template.getIsGarage().equals("是")){
								water.setIsGarage(1);
							}
						}

						if(StrUtil.isNotEmpty(template.getWaterBeginCount())){
							water.setBeginCount(new BigDecimal(template.getWaterBeginCount()));
						}
						if(StrUtil.isNotEmpty(template.getWaterEndCount())){
							water.setEndCount(new BigDecimal(template.getWaterEndCount()));
						}

						if (StrUtil.isNotEmpty(template.getWaterNum())) {
							water.setPaidCount(new BigDecimal(template.getWaterNum()));
						}
						if (StrUtil.isNotEmpty(template.getWaterSinglePrice())) {
							water.setSinglePrice(new BigDecimal(template.getWaterSinglePrice()));
						}
						if (StrUtil.isNotEmpty(template.getWaterPrice())) {
							water.setTotalPrice(new BigDecimal(template.getWaterPrice()));
						}

						if (blWaterExits) {
							ret = orderTemplateMapper.updateById(water);
						} else {
							ret = orderTemplateMapper.insert(water);
						}
						if (ret <= 0) {
							throw new TransactionalException("orderTemplateMapper insert error");
						}
					}
				}

				//加压费
				if(StrUtil.isNotEmpty(template.getJiaYaPrice())) {
					double totalPrice = Double.parseDouble(template.getJiaYaPrice());
					if(totalPrice >= 0){
						boolean blJiaYaExits = true;
						QueryWrapper<PropertyFeeOrderTemplate> qwJiaYa = new QueryWrapper<>();
//						qwJiaYa.eq("real_name", template.getRealName());
						qwJiaYa.eq("room_no", roomNo);
						qwJiaYa.eq("fee_type", "jiaya");
						qwJiaYa.eq("year_num", template.getJiaYaYearNum());
						qwJiaYa.eq("month_num", template.getJiaYaMonthNum());
						qwJiaYa.eq("estate_id", estate.getId());
						qwJiaYa.eq("is_garage", 0);
						if(StrUtil.isNotEmpty(template.getIsGarage())){
							if(template.getIsGarage().equals("是")){
								qwJiaYa.eq("is_garage", 1);
							}
						}
						qwJiaYa.last("limit 1");
						PropertyFeeOrderTemplate jiaya = orderTemplateMapper.selectOne(qwJiaYa);
						if (jiaya == null) {
							blJiaYaExits = false;
							jiaya = new PropertyFeeOrderTemplate();
							jiaya.setEstateId(estateId);
							jiaya.setEstateName(estate.getEstateName());

							jiaya.setYearNum(template.getJiaYaYearNum());
							jiaya.setMonthNum(template.getJiaYaMonthNum());

							jiaya.setRoomType(room.getRoomType());
							jiaya.setFeeType("jiaya");

							jiaya.setPhoneNum(template.getPhoneNum());
							jiaya.setRealName(template.getRealName());
							jiaya.setRoomNo(roomNo);
							jiaya.setBangWxUserId(0);
							jiaya.setRemark(template.getRemark());
							jiaya.setIsBand(0);
							jiaya.setState(0);
							jiaya.setCreateTime(new Date());

							jiaya.setPaidPrice(new BigDecimal(0));
							jiaya.setIsManualData(0);//批量导入
						}

						jiaya.setIsArrears(getIsArrears(template.getJiaYaYearNum(),template.getJiaYaMonthNum()));
						jiaya.setRoomId(room.getId());

						jiaya.setIsGarage(0);
						if(StrUtil.isNotEmpty(template.getIsGarage())){
							if(template.getIsGarage().equals("是")){
								jiaya.setIsGarage(1);
							}
						}

						if (StrUtil.isNotEmpty(template.getJiaYaSinglePrice())) {
							jiaya.setSinglePrice(new BigDecimal(template.getJiaYaSinglePrice()));
						}
						if (StrUtil.isNotEmpty(template.getJiaYaPrice())) {
							jiaya.setTotalPrice(new BigDecimal(template.getJiaYaPrice()));
						}
						if (StrUtil.isNotEmpty(template.getJiaYaYouHuiPrice())) {
							jiaya.setYouhuiPrice(new BigDecimal(template.getJiaYaYouHuiPrice()));
						}

						if (blJiaYaExits) {
							ret = orderTemplateMapper.updateById(jiaya);
						} else {
							ret = orderTemplateMapper.insert(jiaya);
						}
					}
				}

				if (ret <= 0) {
					throw new TransactionalException("orderTemplateMapper insert error");
				}
				successNum ++;
			} catch (Exception e) {
				failureNum++;
				String msg = "<br/>" + failureNum + "、账号 " + template.getRoomNo() + "-" + template.getRealName() + " 导入失败：";
				failureMsg.append(msg + e.getMessage());
				log.error(msg, e);
			}
		}
		if (failureNum > 0) {
			failureMsg.insert(0, "很抱歉，导入失败！共 " + failureNum + " 条数据格式不正确，错误如下：");
			throw new CustomException(failureMsg.toString());
		} else {
			successMsg.insert(0, "恭喜您，数据已全部导入成功！共 " + successNum + " 条，数据如下：");
		}
		return successMsg.toString();
	}

	/**
	 *  是否是欠费账单
	 * @param yearNum
	 * @param monthNum
	 * @return
	 */
	private int getIsArrears(String yearNum,String monthNum){
		int ret = 0;
		int year = Integer.parseInt(yearNum);
		int month = Integer.parseInt(monthNum);
		if(year < 2022 || (year == 2022 && month < 8)){
			ret = 1;
		}
		return ret;
	}
}
